package com.qzdatasoft.mcp.server.mysql.service;

import com.qzdatasoft.mcp.server.mysql.exception.SqlGenerationException;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
public class MySqlToolService {

    public MySqlToolService(@Autowired JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
        HikariDataSource dataSource = (HikariDataSource)Objects.requireNonNull(jdbcTemplate.getDataSource());
        log.info("***********************************************");
        log.info("Spring.datasource info:");
        log.info("jdbcUrl: {}",dataSource.getJdbcUrl());
        log.info("userName: {}",dataSource.getUsername());
        log.info("password: {}",dataSource.getPassword());
        log.info("***********************************************");
    }

    public  List<Map<String, Object>> query(String sql) {

        // TODO: 需要有漏洞防范方案：
        // 1. 如何防止注入风险
        // 2. 如何防止超大数据集查询风险

        if (!sql.toLowerCase().startsWith("select")) {
            throw new SqlGenerationException("Only SELECT queries are allowed.");
        }

        log.info("execute query: {}", sql);

        return jdbcTemplate.queryForList(sql);
    }

    public String  queryAllTableName() {
        String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);

        String re = ObjectUtils.isEmpty(list) ? "没有找到任何表" : linkHeader2ndData(getHeaders(list), getCvs(list));
        log.info("queryAllTableName(): {}", re);

        return re;
    }

    /**
     * 根据表的注释搜索数据库中的表名
     *
     * @param tableComment:
     *     text (str): 要搜索的表注释关键词, 仅支持持单表
     * @return
     *     包含查询结果的TextContent列表
     *     - 返回匹配的表名和表注释信息
     *     - 结果以CSV格式返回
     */
    public String queryTableNameByComment(String tableComment){
        String sql = """
select table_name
     , table_comment
  from information_schema.tables
 where table_schema = database()
   and table_comment like ?""";

        List<Map<String,Object>> list = this.jdbcTemplate.queryForList(this.reduceSpaces(sql), "%"+tableComment.trim()+"%");

        String re = ObjectUtils.isEmpty(list) ? "没有找到匹配的表名" : linkHeader2ndData(getHeaders(list), getCvs(list));
        log.info("queryTableSchema({}): {}", List.of(tableComment), re);

        return re;
    }

    /**
     * 获取指定表的字段结构信息
     *
     * @param tableNames - 要查询的表名，多个表名以逗号分隔
     * @return list[TextContent] - 包含查询结果的TextContent列表
     *    - 包含表名、字段名、字段类型、是否允许为空、字段默认值、字段说明信息
     *    - 结果按表名和字段顺序排序
     *    - 结果以CSV格式返回
     */
    public String queryTableSchema(String... tableNames) {
        String placeholders = String.join(",", java.util.Collections.nCopies(tableNames.length, "?"));
        String sql = """
   select table_name
        , column_name
        , data_type
        , is_nullable
        , column_default
        , column_comment
     from information_schema.columns
    where table_name in (%s)
 order by table_name, ordinal_position
""".formatted(placeholders);

        for (int i = 0; i < tableNames.length; i++) {
            tableNames[i] = tableNames[i].trim();
        }

        List<Map<String,Object>> list = this.jdbcTemplate.queryForList(this.reduceSpaces(sql), (Object[])tableNames);

        String re=ObjectUtils.isEmpty(list) ? "您要查找的表不存在." : linkHeader2ndData(getHeaders(list), getCvs(list));
        log.info("queryTableSchema({}): {}", List.of(tableNames), re);
        return re;
    }

    private String reduceSpaces(String sql){
        while(sql.indexOf("  ")>0){
            sql = sql.replace("  ", " ");
        }
        return sql.trim();
    }

    private final static Function<Object,String> IF_NULL = e -> ObjectUtils.isEmpty(e)? "NULL" : e.toString();

    private static String getCvs(List<Map<String, Object>> list){
        return list.stream().map(
                e -> e.values().stream().map(IF_NULL).collect(Collectors.joining(","))
        ).collect(Collectors.joining("\n"));
    }

    private static String getHeaders(List<Map<String, Object>> list){
        return String.join(",", list.get(0).keySet());
    }

    private static String linkHeader2ndData(String header, String data){
        return String.join("\n", header, data);
    }

    JdbcTemplate jdbcTemplate;
}
